﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Dictionary.Core
{
    public class Vocabulary
    {
        public int id { get; set; }
        public int category_id { get; set; }
        public String value { get; set; }
        public String description { get; set; }
        public String created { get; set; }
        public String updated { get; set; }
        public int status { get; set; }
        /// <summary>
        /// Default constructor
        /// </summary>
        public Vocabulary()
        { }
        /// <summary>
        /// Method check Vocab is exist in Database
        /// </summary>
        /// <returns>bool</returns>
        public bool isExist()
        {
            bool isExist = false;
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM vocabulary WHERE value = \"" + this.value + "\"";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                    isExist = true;
                else
                    isExist = false;
                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return isExist;
        }
        /// <summary>
        /// Constructor with Category_id and Value as Param
        /// </summary>
        /// <param name="category_id">int</param>
        /// <param name="value">String</param>
        public Vocabulary(int category_id, String value)
        {
            this.category_id = category_id;
            this.value = value;
        }
        /// <summary>
        /// Method to select detail vocab
        /// </summary>
        public void select()
        {
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM vocabulary WHERE id = " + this.id;
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    this.id = Convert.ToInt16(dataReader[0]);
                    this.category_id = Convert.ToInt16(dataReader[1]);
                    this.value = dataReader[2].ToString();
                    this.description = dataReader[3].ToString();
                    this.created = dataReader[4].ToString();
                    this.updated = dataReader[5].ToString();
                    this.status = Convert.ToInt16(dataReader[6]);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
        }
        
        /// <summary>
        /// Method to insert new Vocab to databse
        /// </summary>
        /// <returns></returns>
        public bool insert()
        {
            SQLHelper sqlHelper = new SQLHelper();
            if (!this.isExist())
            {
                String strInsert = "INSERT INTO vocabulary(category_id,value,created,updated) " +
                                   "VALUES('" + this.category_id + "','" + this.value + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                if (sqlHelper.executeNonQuery(strInsert) > 0)
                    return true;
                else
                    return false;
            }
            return false;
        }

        /// <summary>
        /// To convert object to string
        /// </summary>
        /// <returns></returns>
        public String display()
        {
            Translate translate = new Translate("English", "Vietnamese", this.value);
            return this.value + " - " + translate.toTranslate();
        }

        /// <summary>
        /// Method to insert list vocab to database
        /// </summary>
        /// <returns></returns>
        public static int insertAll(List<Vocabulary> listVocab,int category_id)
        {
            int count = 0;
            SQLHelper sqlHelper = new SQLHelper();
            if (sqlHelper.OpenConnection())
            {
                MySqlCommand cmd;
                MySqlDataReader dataReader;
                foreach (Vocabulary itemVocab in listVocab)
                {
                    // check vocab is exist in database
                    String strQuery = "SELECT * FROM vocabulary WHERE value = \"" + itemVocab.value + "\"";
                    cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                    dataReader = cmd.ExecuteReader();
                    if (!dataReader.HasRows)
                    {
                        dataReader.Close();
                        // to insert Vocab to database
                        itemVocab.value = itemVocab.value.Replace("\"", "'");
                        String strInsert = "INSERT INTO vocabulary(category_id,value,created,updated) " +
                                   "VALUES('" + category_id + "',\"" + itemVocab.value + "\",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                        cmd = new MySqlCommand(strInsert, sqlHelper.getConnection());
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            count++;
                        }
                    }
                    else
                        dataReader.Close();
                }
            }
            sqlHelper.CloseConnection();
            return count;
        }
        /// <summary>
        /// Method to delete all vocab in category from database
        /// </summary>
        /// <param name="category_id"></param>
        /// <returns></returns>
        public static bool deleteAll(int category_id)
        { 
            SQLHelper sqlHelper = new SQLHelper();
            String strDelete = "DELETE FROM vocabulary WHERE category_id=" + category_id;
            if (sqlHelper.executeNonQuery(strDelete) > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// Method to select all vocab in category
        /// </summary>
        /// <param name="category_id"></param>
        /// <returns></returns>
        public static List<Vocabulary> selectAll(int category_id)
        {
            List<Vocabulary> listResult = new List<Vocabulary>();
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM vocabulary WHERE category_id = " + category_id;
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                int row = 0;
                while (dataReader.Read())
                {
                    Vocabulary temp = new Vocabulary();
                    temp.id = Convert.ToInt16(dataReader[0]);
                    temp.category_id = Convert.ToInt16(dataReader[1]);
                    temp.value = dataReader[2].ToString();
                    temp.description = dataReader[3].ToString();
                    temp.created = dataReader[4].ToString();
                    temp.updated = dataReader[5].ToString();
                    temp.status = Convert.ToInt16(dataReader[6]);
                    row++;
                    listResult.Add(temp);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return listResult;
        }
        /// <summary>
        /// Metho to search Vocab in side Category
        /// </summary>
        /// <param name="category_id"></param>
        /// <param name="vocab"></param>
        /// <returns></returns>
        public static List<Vocabulary> search(int category_id, String vocab)
        {
            vocab = vocab.Replace("\"", "");
            List<Vocabulary> listResult = new List<Vocabulary>();
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM vocabulary WHERE value LIKE \"%" + vocab + "%\"";
            if (category_id != 0)
            {
                strQuery += " AND category_id = " + category_id;
            }
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                int row = 0;
                while (dataReader.Read())
                {
                    Vocabulary temp = new Vocabulary();
                    temp.id = Convert.ToInt16(dataReader[0]);
                    temp.category_id = Convert.ToInt16(dataReader[1]);
                    temp.value = dataReader[2].ToString();
                    temp.description = dataReader[3].ToString();
                    temp.created = dataReader[4].ToString();
                    temp.updated = dataReader[5].ToString();
                    temp.status = Convert.ToInt16(dataReader[6]);
                    row++;
                    listResult.Add(temp);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return listResult;
        }

        /// <summary>
        /// To get count vocab in database
        /// </summary>
        /// <returns>int</returns>
        public static int count()
        {
            int count = 0;
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT COUNT(*) AS 'count' FROM vocabulary";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    count = Convert.ToInt16(dataReader["count"]);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return count;
        }
    }
}
